package com.bk.dao;

import com.bk.model.User;
import com.bk.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author Ayi
 */
public class UserDao {
    // 获取数据库中的用户表，以结果集返回
    public ResultSet getUserTable() throws SQLException, ClassNotFoundException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from user";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        return rs;
    }

    // 获取数据库中的黑名单，以结果集返回
    public ResultSet getblackListTable() throws SQLException, ClassNotFoundException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from blacklist";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        return rs;
    }

    // 加入黑名单
    public void addblackListTable(User user) throws SQLException {
        Connection connection = DbUtil.getConnection();
        // 用户加入黑名单
        String sql0 = "insert into blacklist values (?, ?, ?, ?, ?, ?)";
        PreparedStatement ps0 = connection.prepareStatement(sql0);
        ps0.setString(1, user.getUid());
        ps0.setString(2, user.getUname());
        ps0.setString(3, user.getUpassword());
        ps0.setString(4, user.getUbooknums());
        ps0.setString(5, user.getUcredit());
        ps0.setString(6, user.getIsAdmin());
        ps0.executeUpdate();
        // 从用户表中删除
        String sql1 = "delete from user where uid = ?";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setString(1, user.getUid());
        ps1.executeUpdate();
    }

    // 移出黑名单
    public void outblackListTable(User user) throws SQLException {
        Connection connection = DbUtil.getConnection();
        // 用户加入用户表
        String sql0 = "insert into user values (?, ?, ?, ?, ?, ?)";
        PreparedStatement ps0 = connection.prepareStatement(sql0);
        ps0.setString(1, user.getUid());
        ps0.setString(2, user.getUname());
        ps0.setString(3, user.getUpassword());
        ps0.setString(4, user.getUbooknums());
        ps0.setString(5, user.getUcredit());
        ps0.setString(6, user.getIsAdmin());
        ps0.executeUpdate();
        // 从黑名单中删除
        String sql1 = "delete from blacklist where blid = ?";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setString(1, user.getUid());
        ps1.executeUpdate();
    }

    // 永久删除一个用户
    public void foreverOutblackListTable(User user) throws SQLException {
        Connection connection = DbUtil.getConnection();
        // 从黑名单中删除
        String sql = "delete from blacklist where blid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, user.getUid());
        ps.executeUpdate();
    }

    // 编辑一个用户的信息
    public boolean editUser(User user, String uname, String upassword, String ucredit) throws SQLException {
        Connection connection = DbUtil.getConnection();

        String sql0 = "update user set uname = ? where uid = ?";
        PreparedStatement ps0 = connection.prepareStatement(sql0);
        ps0.setString(1, uname);
        ps0.setString(2, user.getUid());

        String sql1 = "update user set upassword = ? where uid = ?";
        PreparedStatement ps1 = connection.prepareStatement(sql1);
        ps1.setString(1, upassword);
        ps1.setString(2, user.getUid());

        String sql2 = "update user set ucredit = ? where uid = ?";
        PreparedStatement ps2 = connection.prepareStatement(sql2);
        ps2.setString(1, ucredit);
        ps2.setString(2, user.getUid());

        int r0 = ps0.executeUpdate();
        int r1 = ps1.executeUpdate();
        int r2 = ps2.executeUpdate();
        boolean res = r0 > 0 && r1 > 0 && r2 > 0 ? true : false;
        return res;
    }

    // 在用户表根据id找到一个用户
    public User searchUserById(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        // SQL
        String sql = "select * from user where uid = " + idOrNameInfo;
        // 预处理
        PreparedStatement ps = connection.prepareStatement(sql);
        // 结果集
        ResultSet rs = ps.executeQuery();
        // 返回结果集用户（唯一）
        if (rs.next()) {
            User resUser = new User(rs.getString("uname"));
            resUser.setUid(rs.getString("uid"));
            resUser.setUpassword(rs.getString("upassword"));
            resUser.setUbooknums(rs.getString("ubooknums"));
            resUser.setUcredit(rs.getString("ucredit"));
            resUser.setIsAdmin(rs.getString("isAdmin"));
            return resUser;
        }else{
            return null;
        }
    }

    // 在用户表根据用户名找到一个用户
    public User searchUserByName(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        // SQL
        String sql = "select * from user where uname = ?";
        // 预处理
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, idOrNameInfo);
        // 结果集
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
        // 返回结果集用户（唯一）
            User resUser = new User(rs.getString("uname"));
            resUser.setUid(rs.getString("uid"));
            resUser.setUpassword(rs.getString("upassword"));
            resUser.setUbooknums(rs.getString("ubooknums"));
            resUser.setUcredit(rs.getString("ucredit"));
            resUser.setIsAdmin(rs.getString("isAdmin"));
            return resUser;
        }else{
            return null;
        }
    }

    // 在黑名单根据id找到一个用户
    public User searchBlUserById(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from blacklist where blid = " + idOrNameInfo;
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            User resUser = new User(rs.getString("blname"));
            resUser.setUid(rs.getString("blid"));
            resUser.setUpassword(rs.getString("blpassword"));
            resUser.setUbooknums(rs.getString("blbooknums"));
            resUser.setUcredit(rs.getString("blcredit"));
            resUser.setIsAdmin(rs.getString("blIsAdmin"));
            return resUser;
        }else{
            return null;
        }
    }

    // 在黑名单根据用户名找到一个用户
    public User searchBlUserByName(String idOrNameInfo) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from blacklist where blname = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, idOrNameInfo);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            User resUser = new User(rs.getString("blname"));
            resUser.setUid(rs.getString("blid"));
            resUser.setUpassword(rs.getString("blpassword"));
            resUser.setUbooknums(rs.getString("blbooknums"));
            resUser.setUcredit(rs.getString("blcredit"));
            resUser.setIsAdmin(rs.getString("blIsAdmin"));
            return resUser;
        }else{
            return null;
        }
    }

    // 更新用户借阅量
    public void updateBooknums(User user, int booknums) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "update user set ubooknums = ? where uid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, Integer.toString(booknums));
        ps.setString(2, user.getUid());
        ps.executeUpdate();
    }

    public boolean searchInBlackList(String uid) throws SQLException {
        Connection connection = DbUtil.getConnection();
        String sql = "select * from blacklist where blid = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, uid);
        return ps.executeQuery().next() ? true : false;
    }
}
